﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;

namespace DAO
{
    public class MatHangDAO
    {
        public List<MatHangDTO> LayDSMH()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT * FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();

                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.MaXuatXu = reader.GetInt32(1);
                mhDTO.TenMatHang = reader.GetString(2);
                mhDTO.SoLuong = reader.GetInt32(3);
                mhDTO.GiaNhap = reader.GetDouble(4);
                mhDTO.MaDonViTinh = reader.GetInt32(5);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<MatHangDTO> LayDanhSachTenMatHang()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT DISTINCT MaMatHang, TenMatHang FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();
                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.TenMatHang = reader.GetString(1);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<XuatXuDTO> LayXuatXuTheoMatHang(int maMH)
        {
            List<XuatXuDTO> danhsach = new List<XuatXuDTO>();
            string sql = "Select xx.MaXuatXu, xx.TenXuatXu From XUATXU xx, MATHANG mh Where xx.MaXuatXu = mh.MaXuatXu and mh.MaMatHang = " +  maMH ;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                XuatXuDTO xxDTO = new XuatXuDTO();
                xxDTO.MaXuatXu = reader.GetInt32(0);
                xxDTO.TenXuatXu = reader.GetString(1);
                danhsach.Add(xxDTO);
            }
            conn.Close();
            return danhsach;
        }
        public int LaySoLuongTheoMatHang(int maMH, int maXX)
        {
            int soluong = 0;
            string sql = "Select SoLuong From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                soluong = reader.GetInt32(0);
            }
            conn.Close();
            return soluong;
        }
        public double LayGiaTheoMatHang(int maMH, int maXX)
        {
            double gia = 0;
            string sql = "Select GiaNhap From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                gia = reader.GetDouble(0);
            }
            conn.Close();
            return gia;
        }
        public string LayTenMatHangTheoMa(int maMH)
        {
            string tenmh = "";
            string sql = "Select TenMatHang From MATHANG Where MaMatHang= " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tenmh = reader.GetString(0);
            }
            conn.Close();
            return tenmh;
        }
        public int LayMaMatHangTheoTen(string ten)
        {
            int ma = 0;
            string sql = "Select MaMatHang From MATHANG Where TenMatHang= " + "N'"+ten+"'";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ma = reader.GetInt32(0);
            }
            conn.Close();
            return ma;
        }
        public string CapNhatSoLuong(int maMH, int maXX, int soluong, bool err)
        {
            string mess = "";
            int tonkho = LaySoLuongTheoMatHang(maMH, maXX);
            if (err)
                tonkho = tonkho + soluong;
            else
                tonkho = tonkho - soluong;
            string sql = "Update MATHANG SET SoLuong = " + tonkho + " Where MaMatHang = " + maMH + " and MaXuatXu = " + maXX;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }

    }
}
